--    Author    : ChenErHao
--    Name      : ADM.CUBE_LOAN
--    Functions : 
--    Purpose   : Daily saving the variation data from stg
--    Revisions or Comments
--    VER        DATE        AUTHOR           DESCRIPTION
--   ---------  ----------  ---------------  ------------------------------------
--    1.0        2019-03-11  ChenErHao       1.CREATE THE PROCEDURE
--
INSERT  OVERWRITE TABLE ADM.CUBE_LOAN PARTITION (DATA_DATE = '#V_DATA_DATE#',DATA_SRC_ORG)
SELECT 
   FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-MM-dd HH:mm:ss') AS BATCH_DATE                                                            -- 跑批日期
  ,DATA_ORG_CODE                                                                                                                  -- 金融机构编码
  ,CUSTOMER_TYPE                                                                                                                  -- 客户类型
  ,LPIC_SUB                                                                                                                       -- 贷款主体行业类别
  ,REGIST_ADD_CODE                                                                                                                -- 借款人注册地编码
  ,ENTERPRISE_INVESTOR                                                                                                            -- 企业出资人经济成分
  ,ENTERPRISE_SCALE                                                                                                               -- 企业规模
  ,PRODUCT_CATEGORY                                                                                                               -- 产品类别
  ,LOANS_ACTUALLY                                                                                                                 -- 贷款实际投向
  ,LOAN_QUALITY                                                                                                                   -- 贷款质量
  ,OVER_YUQI_ID                                                                                                                   -- 逾期期限
  ,SURP_PERIOD_TYPE_ID                                                                                                            -- 剩余期限区间
  ,OVER_ZHANQI_ID                                                                                                                 -- 是否展期
  ,RATE_FIXED_FLOAT                                                                                                               -- 利率是否固定
  ,GUARANTY_STYLE                                                                                                                 -- 贷款担保方式
  ,LOAN_STATUS                                                                                                                    -- 贷款状态
  ,LOAN_GRANT_RECOVER                                                                                                             -- 发放回收标识
  ,LOANS_PERIOD                                                                                                                   -- 贷款期限区间_多维
  ,AMT_INTERVAL_ID                                                                                                                -- 金额区间
  ,RATE_LEVEL_QJ                                                                                                                  -- 利率区间
  ,RATE_LEVEL_FDQJ                                                                                                                -- 利率浮动区间
  ,DATA_ORG_AREA                                                                                                                 -- 数据机构所在地区
  ,LEGAL_ORG_AREA                                                                                                                 -- 法人机构所在地区
  ,SUM(IF(DATA_DATE='#V_DATA_DATE#',LOAB_BALANCE,0)) AS LOAB_BALANCE                                                              -- 贷款余额
  ,SUM(IF(DATA_DATE='#V_LAST_1M_END_DATE#',LOAB_BALANCE,0)) AS LOAB_BALANCE_LM                                                    -- 上月贷款余额
  ,SUM(IF(DATA_DATE='#V_LAST_12M_END_DATE#',LOAB_BALANCE,0)) AS LOAB_BALANCE_LY                                                   -- 去年同期贷款余额
  ,SUM(IF(DATA_DATE='#V_LAST_1Y_END_DAY#',LOAB_BALANCE,0)) AS LOAB_BALANCE_LYM                                                    -- 上年末贷款余额
  ,SUM(IF(DATA_DATE='#V_DATA_DATE#',LOAF_BALANCE,0)) AS LOAF_BALANCE                                                              -- 贷款发生额
  ,SUM(IF(DATA_DATE='#V_LAST_1M_END_DATE#',LOAF_BALANCE,0)) AS LOAF_BALANCE_LM                                                    -- 上月贷款发生额
  ,SUM(IF(DATA_DATE='#V_LAST_12M_END_DATE#',LOAF_BALANCE,0)) AS LOAF_BALANCE_LY                                                   -- 去年同期贷款发生额
  ,SUM(IF(DATA_DATE='#V_LAST_1Y_END_DAY#',LOAF_BALANCE,0)) AS LOAF_BALANCE_LYM                                                    -- 上年末贷款发生额
  ,SUM(IF(DATA_DATE='#V_DATA_DATE#',LOAB_JQ_SHENGYU_PERIOD,0)) AS LOAB_JQ_SHENGYU_PERIOD                                          -- 贷款余额剩余期限加权值
  ,SUM(IF(DATA_DATE='#V_DATA_DATE#',LOAF_JQ_SHENGYU_PERIOD,0)) AS LOAF_JQ_SHENGYU_PERIOD                                          -- 贷款发生额剩余期限加权值
  ,SUM(IF(DATA_DATE='#V_DATA_DATE#',LOAN_LOAB_LEVEL,0)) AS LOAN_LOAB_LEVEL                                                        -- 余额加权值
  ,SUM(IF(DATA_DATE='#V_DATA_DATE#',LOAN_LOAF_LEVEL,0)) AS LOAN_LOAF_LEVEL                                                        -- 发生额加权值
  ,SUM(IF(DATA_DATE='#V_DATA_DATE#',LOAB_STOCK_COUNT,0)) AS LOAB_STOCK_COUNT                                                      -- 余额笔数
  ,SUM(IF(DATA_DATE='#V_DATA_DATE#',LOAF_STOCK_COUNT,0)) AS LOAF_STOCK_COUNT                                                      -- 发生额笔数
  ,SUM(IF(DATA_DATE='#V_DATA_DATE#',LOAB_RATE_LEVEL,0)) AS LOAB_RATE_LEVEL                                                        -- 余额利率水平
  ,SUM(IF(DATA_DATE='#V_DATA_DATE#',LOAF_RATE_LEVEL,0)) AS LOAF_RATE_LEVEL                                                        -- 发生额利率水平
  ,DATA_SRC_ORG
FROM
(
   SELECT 
     DATA_ORG_CODE                                                                            -- 金融机构编码
     ,CUSTOMER_TYPE                                                                            -- 客户类型
     ,LPIC_SUB                                                                                 -- 贷款主体行业类别
     ,REGIST_ADD_CODE                                                                          -- 借款人注册地编码
     ,ENTERPRISE_INVESTOR                                                                      -- 企业出资人经济成分
     ,ENTERPRISE_SCALE                                                                         -- 企业规模
     ,PRODUCT_CATEGORY                                                                         -- 产品类别
     ,LOANS_ACTUALLY                                                                           -- 贷款实际投向
     ,LOAN_BALANCE AS LOAB_BALANCE                                                             -- 贷款余额
     ,0 AS LOAF_BALANCE                                                                        -- 贷款发生额
     ,LOAN_QUALITY                                                                             -- 贷款质量
     ,OVER_YUQI_ID                                                                             -- 逾期期限
     ,CASE WHEN SHENGYU_PERIOD_M <= 0 THEN '9999'
         WHEN SHENGYU_PERIOD_M <=6 THEN 'A'
         WHEN SHENGYU_PERIOD_M <=12 THEN 'B'
         WHEN SHENGYU_PERIOD_M <=36 THEN 'C'
         WHEN SHENGYU_PERIOD_M <=60 THEN 'D'
         WHEN SHENGYU_PERIOD_M <=120 THEN 'E'
         ELSE '9999'
      END AS SURP_PERIOD_TYPE_ID                                                               -- 剩余期限区间
     ,LOAN_BALANCE*SHENGYU_PERIOD_M AS LOAB_JQ_SHENGYU_PERIOD                                  -- 贷款余额加权剩余期限
     ,0 AS LOAF_JQ_SHENGYU_PERIOD                                                              -- 贷款发生额剩余期限加权值
     ,OVER_ZHANQI_ID                                                                           -- 是否展期
     ,RATE_FIXED_FLOAT                                                                         -- 利率是否固定
     ,GUARANTY_STYLE                                                                           -- 贷款担保方式
     ,LOAN_STATUS                                                                              -- 贷款状态
     ,'9999' AS LOAN_GRANT_RECOVER                                                             -- 发放回收标识
     ,LOANS_PERIOD_CUB AS LOANS_PERIOD                                                         -- 贷款期限区间_多维
     ,LOAN_OCCURRENCE_LEVEL AS LOAN_LOAB_LEVEL                                                 -- 贷款余额加权金额
     ,0 AS LOAN_LOAF_LEVEL                                                                     -- 发生额额加权金额
     ,AMT_INTERVAL_ID                                                                          -- 金额区间
     ,STROKE_COUNT AS LOAB_STOCK_COUNT                                                         -- 余额笔数
     ,0 AS LOAF_STOCK_COUNT                                                                    -- 发生额笔数
     ,RATE_LEVEL_QJ                                                                            -- 利率区间
     ,CASE WHEN BASE_RATE=0  THEN 9999
             WHEN (RATE_LEVEL-BASE_RATE)/BASE_RATE*100 <= -30 THEN 10
             WHEN (RATE_LEVEL-BASE_RATE)/BASE_RATE*100 <= -10 THEN 20
             WHEN (RATE_LEVEL-BASE_RATE)/BASE_RATE*100 <= 0 THEN 21
             WHEN (RATE_LEVEL-BASE_RATE)/BASE_RATE*100 =0  THEN 30
             WHEN (RATE_LEVEL-BASE_RATE)/BASE_RATE*100 <= 10  THEN 40
             WHEN (RATE_LEVEL-BASE_RATE)/BASE_RATE*100 <= 30  THEN 50
             WHEN (RATE_LEVEL-BASE_RATE)/BASE_RATE*100 <= 50 THEN 60
             WHEN (RATE_LEVEL-BASE_RATE)/BASE_RATE*100 <= 100 THEN 70
             WHEN (RATE_LEVEL-BASE_RATE)/BASE_RATE*100 > 100  THEN 80  
             ELSE 9999
         END AS RATE_LEVEL_FDQJ                                                                  -- 利率浮动区间
     ,BELONG_AREA_CODE AS DATA_ORG_AREA                                                           -- 数据机构所在地区
     ,T2.LEGAL_ORG_AREA                                                                        -- 法人机构所在地区
     ,RATE_LEVEL AS LOAB_RATE_LEVEL                                                            -- 余额利率水平
     ,0 AS LOAF_RATE_LEVEL                                                                     -- 发生额利率水平
     ,DATA_DATE
     ,DATA_SRC_ORG
   FROM EDW.LOAB_INFO T
   LEFT JOIN DMCODE.T_ORG_MANAGE T2 ON T.DATA_ORG_CODE = T2.DATA_ORG_ID
   WHERE T.DATA_DATE IN ('#V_DATA_DATE#','#V_LAST_1M_END_DATE#','#V_LAST_12M_END_DATE#','#V_LAST_1Y_END_DAY#') -- 本期，上月末，上年同期，去年年末
   
   UNION ALL 
   
   SELECT 
     DATA_ORG_CODE                                                                           -- 金融机构编码
     ,CUSTOMER_TYPE                                                                           -- 客户类型
     ,LPIC_SUB                                                                                -- 贷款主体行业类别
     ,REGIST_ADD_CODE                                                                         -- 借款人注册地编码
     ,ENTERPRISE_INVESTOR                                                                     -- 企业出资人经济成分
     ,ENTERPRISE_SCALE                                                                        -- 企业规模
     ,PRODUCT_CATEGORY                                                                        -- 产品类别
     ,LOANS_ACTUALLY                                                                          -- 贷款实际投向
     ,0 AS LOAN_BALANCE                                                                       -- 贷款余额
     ,LOAN_OCCURRENCE AS LOAF_BALANCE                                                         -- 贷款发生额
     ,9999 AS LOAN_QUALITY                                                                    -- 贷款质量
     ,9999 AS OVER_YUQI_ID                                                                    -- 逾期期限
     ,CASE WHEN SHENGYU_PERIOD_M <= 0 THEN '9999'
         WHEN SHENGYU_PERIOD_M <=6 THEN 'A'
         WHEN SHENGYU_PERIOD_M <=12 THEN 'B'
         WHEN SHENGYU_PERIOD_M <=36 THEN 'C'
         WHEN SHENGYU_PERIOD_M <=60 THEN 'D'
         WHEN SHENGYU_PERIOD_M <=120 THEN 'E'
         ELSE '9999'
      END AS SURP_PERIOD_TYPE_ID                                                              -- 剩余期限区间
     ,0 AS LOAB_JQ_SHENGYU_PERIOD                                                             -- 贷款余额加权期限
     ,LOAN_OCCURRENCE*SHENGYU_PERIOD_M                                                        -- 贷款发生额加权期限
     ,9999 AS OVER_ZHANQI_ID                                                                  -- 是否展期
     ,RATE_FIXED_FLOAT                                                                        -- 利率是否固定
     ,GUARANTY_STYLE                                                                          -- 贷款担保方式
     ,LOAN_STATUS                                                                             -- 贷款状态
     ,LOAN_GRANT_RECOVER                                                                      -- 发放回收标识
     ,LOANS_PERIOD_CUB AS  LOANS_PERIOD                                                      -- 贷款期限区间_多维
     ,0 AS LOAN_LOAB_LEVEL                                                                    -- 余额加权值
     ,LOAN_OCCURRENCE_LEVEL AS LOAN_LOAF_LEVEL                                                -- 发生额加权值
     ,AMT_INTERVAL_ID                                                                         -- 金额区间
     ,0 AS LOAB_STOCK_COUNT                                                                   -- 余额笔数
     ,STROKE_COUNT AS LOAF_STOCK_COUNT                                                        -- 发生额笔数
     ,RATE_LEVEL_QJ                                                                           -- 利率区间
     ,CASE WHEN BASE_RATE=0  THEN 9999
             WHEN (RATE_LEVEL-BASE_RATE)/BASE_RATE*100 <= -30 THEN 10
             WHEN (RATE_LEVEL-BASE_RATE)/BASE_RATE*100 <= -10 THEN 20
             WHEN (RATE_LEVEL-BASE_RATE)/BASE_RATE*100 <= 0 THEN 21
             WHEN (RATE_LEVEL-BASE_RATE)/BASE_RATE*100 =0  THEN 30
             WHEN (RATE_LEVEL-BASE_RATE)/BASE_RATE*100 <= 10  THEN 40
             WHEN (RATE_LEVEL-BASE_RATE)/BASE_RATE*100 <= 30  THEN 50
             WHEN (RATE_LEVEL-BASE_RATE)/BASE_RATE*100 <= 50 THEN 60
             WHEN (RATE_LEVEL-BASE_RATE)/BASE_RATE*100 <= 100 THEN 70
             WHEN (RATE_LEVEL-BASE_RATE)/BASE_RATE*100 > 100  THEN 80  
             ELSE 9999
         END AS RATE_LEVEL_FDQJ                                                                  -- 利率浮动区间
     ,BELONG_AREA_CODE AS DATA_ORG_AREA                                                        -- 数据机构所在地区
     ,T2.LEGAL_ORG_AREA                                                                        -- 法人机构所在地区
     ,0 AS LOAB_RATE_LEVEL                                                                     -- 余额利率水平
     ,RATE_LEVEL AS LOAF_RATE_LEVEL                                                            -- 发生额利率水平
     ,DATA_DATE
     ,DATA_SRC_ORG
   FROM EDW.LOAF_INFO T
   LEFT JOIN DMCODE.T_ORG_MANAGE T2 ON T.DATA_ORG_CODE = T2.DATA_ORG_ID
   WHERE T.DATA_DATE IN ('#V_DATA_DATE#','#V_LAST_1M_END_DATE#','#V_LAST_12M_END_DATE#','#V_LAST_1Y_END_DAY#') -- 本期，上月末，上年同期，去年年末
) T
GROUP BY 
   DATA_ORG_CODE                                                                            -- 金融机构编码
  ,CUSTOMER_TYPE                                                                            -- 客户类型
  ,LPIC_SUB                                                                                 -- 贷款主体行业类别
  ,REGIST_ADD_CODE                                                                          -- 借款人注册地编码
  ,ENTERPRISE_INVESTOR                                                                      -- 企业出资人经济成分
  ,ENTERPRISE_SCALE                                                                         -- 企业规模
  ,PRODUCT_CATEGORY                                                                         -- 产品类别
  ,LOANS_ACTUALLY                                                                           -- 贷款实际投向
  ,LOAN_QUALITY                                                                             -- 贷款质量
  ,OVER_YUQI_ID                                                                             -- 逾期期限
  ,SURP_PERIOD_TYPE_ID                                                                      -- 剩余期限区间
  ,OVER_ZHANQI_ID                                                                           -- 是否展期
  ,RATE_FIXED_FLOAT                                                                         -- 利率是否固定
  ,GUARANTY_STYLE                                                                           -- 贷款担保方式
  ,LOAN_STATUS                                                                              -- 贷款状态
  ,LOAN_GRANT_RECOVER                                                                       -- 发放回收标识
  ,LOANS_PERIOD                                                                             -- 贷款期限区间_多维
  ,AMT_INTERVAL_ID                                                                          -- 金额区间
  ,RATE_LEVEL_QJ                                                                            -- 利率区间
  ,RATE_LEVEL_FDQJ                                                                          -- 利率浮动区间
  ,DATA_ORG_AREA                                                                           -- 数据机构所在地区
  ,LEGAL_ORG_AREA                                                                           -- 法人机构所在地区
  ,DATA_SRC_ORG
;
